# encoding: utf-8
__author__ = 'wwj'
import sys
reload(sys)
sys.setdefaultencoding('utf8')
import pymongo
import re
import MySQLdb
import MySQLdb.cursors
from datetime import *
import paramiko
sys.path.append("/mnt/crawler/girl/dbShell/")
import conf
from conf import myException
from scrapy.mail import MailSender
import traceback
from collections import defaultdict,OrderedDict
from datetime import *
import redis
from pprint import pprint as pp
import sh
class dbshell(object):
    mailer = MailSender()
    sqlpath = None
    mongofrom = None
    urlpool = set()
    datas = []
    data = []
    orderItem = None
    exist = None
    myException = myException
    mongoTable = None
    where = {}
    urlpoolkey = None
    pool = redis.ConnectionPool(host='localhost', port=6379)
    redisconn = redis.Redis(connection_pool=pool)

    def __init__(self,spidername=''):
        self.spidername = spidername or self.spidername
        self.config = getattr(conf,spidername)
        self.client = pymongo.MongoClient(self.config.get('mongoIp'),socketKeepAlive=True,maxPoolSize=500)
        self.client.admin.authenticate(self.config.get('mongoUser'), self.config.get('mongoPwd'), mechanism='SCRAM-SHA-1')
        self.mongo = self.client[self.config.get('mongoDb')][self.mongoTable]
        self.job = self.client[self.config.get('mongoDb')]["job"]
        self.sqlpath = '/Users/wwj/Downloads/%s.sql' % self.mysqlTable
        self.release_dir = '/tmp/%s.sql' % self.mongoTable
        self.mongofrom = self.mongo.find(self.where)

    def __call__(self):
        print "clean"
        self._clean()
        print "buildsql"
        self.buildsql()
        print "insertdb"
        # self.insertdb()
        self.close()

    def _clean(self):
        for x in self.mongofrom:
            self.data = OrderedDict(self.orderItem)
            try:
                # if not x.get("url"):
                #     continue
                tmp = x.get("url").replace("https://www.cityheaven.net","")
                tmp = tmp.replace("http://www.dmm.co.jp","")
                tmp = tmp.replace("http://lovehotel-navi.jp","")
                if self.redisconn.sismember(self.mysqlTable + "Url",tmp):
                    print "!!!!!!!!" + x.get("url","")
                    continue
                else:
                    self.clean(x)
                    print "########" + x.get("url","")
            except AttributeError,e:
                self.job.insert({"date":str(date.today()),"msg":traceback.format_exc(),"name":self.spidername,"url":x["url"]})
                traceback.print_exc()
                continue
            except myException,e:
                print e.message
            except Exception ,e:
                self.job.insert({"date":str(date.today()),"msg":traceback.format_exc(),"name":self.spidername,"url":x["url"]})
                traceback.print_exc()
                continue
                # import ipdb;ipdb.set_trace()
                # log.msg(traceback.format_exc(),level=log.ERROR)
            else:
                if not self.data["url"]:
                    import ipdb;ipdb.set_trace()
                # self.urlpool.add(x.get("url"))
                self.datas.append(self.data)
        # else:
        #     self.job.insert({"date":str(date.today()),"msg":"no mongofrom","name":self.spidername})
        #     raise Exception("no mongofrom")


    def insertdb(self):
        conn = MySQLdb.connect(self.mysqlTable,self.config['release']['mysqlUser'],self.config['release']['mysqlUser'], self.config['mysqlDb'],charset="utf8mb4",cursorclass=MySQLdb.cursors.DictCursor)
        sql = "source %s" % self.sqlpath
        import ipdb;ipdb.set_trace()
        cursor = conn.cursor()
        cursor.execute("source %s" % self.sqlpath)

    def buildsql(self):
        # conn = MySQLdb.connect("127.0.0.1","root","123456789", "shantianjun",charset="utf8mb4",cursorclass=MySQLdb.cursors.DictCursor)
        conn = MySQLdb.connect(self.config['release']['ip'],self.config['release']['mysqlUser'],self.config['release']['mysqlUser'], self.config['mysqlDb'],charset="utf8mb4",cursorclass=MySQLdb.cursors.DictCursor)
        cursor = conn.cursor()
        if len(self.datas) == 0:
            return "not data"
            # raise Exception("ddd")
        keys = self.datas[0].keys()
        # import ipdb;ipdb.set_trace()
        values = [self.datas[i:i+30] for i in range(0, len(self.datas), 30)]
        dd = []
        data_values = "(" + "%s," * (len(keys)) + ")"
        data_values = data_values.replace(',)', ')')
        keys = str(tuple(keys)).replace("'",'')
        for value in values:
            dd = []
            sql = "insert ignore into %s %s values %s" % (self.mysqlTable,keys,data_values)
            for v in value:
                dd.append((MySQLdb.escape_string(str(y).strip().replace("%","")) for yy,y in v.iteritems()))
            try:
                pp(dd)
                cursor.executemany(sql,dd)
                conn.commit()
            except MySQLdb.Error as ex:
                import ipdb;ipdb.set_trace()
                # code, message = ex.args
                m = re.search(r".* at row (\d+)$", message)
                if m:
                    i = int(m.group(1))

        cursor.close()
        conn.commit()

    def getdb(self,sql):
        conn = MySQLdb.connect("127.0.0.1","root","123456789", "shantianjun",charset="utf8mb4",cursorclass=MySQLdb.cursors.DictCursor)
        cursor = conn.cursor()
        cursor.execute(sql)
        tmp = cursor.fetchallDict()
        return tmp
    # 生成批量入库sql文件




    def geturltool(self,sql):
        # urldb = self.client[self.config.get('mongoDb')][self.spidername+"url"]
        # self.urltool = urldb.find({},{"url":1})
        # if self.urltool.count() == 0:
        tmp = self.getdb(sql)
        self.redisconn.sadd(self.mysqlTable + "Url",*map(lambda a:a.get("url").strip(),tmp))
        # urldb.insert_many([{'url': i} for i in tmp])
        # self.urltool = urldb.find({},{"url":1})
        # self.urltool = [x["url"] for x in self.urltool]

    # def getmon(self,name):
    #     self.client[self.config.get('mongoDb')]["girlUrl"].insert_many([{'url': i} for i in stdout.readlines()])
    #     self.client[self.config.get('mongoDb')]["storeUrl"].insert_many(stdout.readlines())
    #     return stdout.read()


    def close(self):
        pass
